﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace CommClass
{
    class MO
    {
        private DBSource ds;
        public MO(DBSource dbSource)
        {
            this.ds=dbSource ;
        }
        //更新库存
        public int updateStork(string sqlcase,string StockTakingid,SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = " update t_StockTaking set " + sqlcase + " where StockTakingid=" + StockTakingid;
                      
            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }


        //更新库存
        public int updateStorkbyDOEntryID(string DOEntryID, SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = " update t_StockTaking  set StockUseableQty= StockUseableQty+a.StockQty from (SELECT StockTakingid,StockQty  FROM [t_chukukucun] where  [DOEntryID]=" + DOEntryID + ") a  where  a.StockTakingid = t_StockTaking.StockTakingid ";

            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }

        //删除库存
        public int deletStork(string sqlcase, SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = " DELETE FROM  t_StockTaking where " + sqlcase ;

            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }
        //删除库存
        public int deletchukukucun(string sqlcase, SqlConnection conn, SqlCommand cmd) 
        {
            string sqlstr = " DELETE FROM  t_chukukucun where " + sqlcase;

            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }
        //插入出库明细
        public int insertchukukucun(string DOEntryID, string StorageID,string StockQty,string Batch,string ProduceDate,string StockPinZhiID,string StockTakingid, SqlConnection conn, SqlCommand cmd)
        {
            if (StorageID == null || StorageID == "")
            {
                StorageID = "null";
            }
            if (StockPinZhiID == null || StockPinZhiID == "")
            {
                StockPinZhiID = "null";
            }
            if (ProduceDate == null || ProduceDate == "")
            {
                ProduceDate = "null";
            }
            else
            {
                ProduceDate = "'" + ProduceDate + "'";
            }
            string sqlstr = " insert  into  t_chukukucun (DOEntryID,StorageID,StockQty,Batch,ProduceDate,StockPinZhiID,StockTakingid) values (" + DOEntryID + "," + StorageID + "," + StockQty + ",'" + Batch + "'," + ProduceDate + "," + StockPinZhiID + ",'" + StockTakingid + "')";

            object obj = App.GetSingle(sqlstr, null, conn, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        //获取合并拣货单各个提单的明细数据
        public DataTable GetDOEntry(string where,SqlCommand cmd)
        {
            DataSet dt = new DataSet();
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append(" SELECT a.[DeliveryNumber],a.[ProdID],a.[OrderQty],a.[SentQty],a.[UnitWeight],a.[Weight],a.[Price],a.[NoTaxAmt],a.[Tax],a.[Amount],a.[Batch],a.[DOEntryID],c.[StoreID] FROM [t_DOEntry] a,[t_DO] b,[t_Store] c  where   a.[DeliveryNumber]= b.[DeliveryNumber] and c.[StoreName]=b.[Stock] ");
            if (where != null && where != "")
            {
                sqlsb.Append(" and " + where);
            }

            dt = App.Query(sqlsb.ToString(), cmd);
            return dt.Tables[0];
        }

        //获取合并拣货单各个提单的明细数据的出库明细
        public DataTable GetDOEntrychuku(string where, SqlCommand cmd)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append(" SELECT [chukukucunID] ,[DOEntryID],[StorageID],[StockQty],[Batch],[ProduceDate],[StockPinZhiID],[StockTakingid]   FROM [t_chukukucun] ");
            if (where != null && where != "")
            {
                sqlsb.Append(" where " + where);
            }

            dt = App.Query(sqlsb.ToString(), cmd);
            return dt.Tables[0];
        }

        //判断是否能够完成集货（必须所有产品都分配完货位）
        public DataTable Getjihuo(string where, SqlCommand cmd)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append(" SELECT b.[OrderQty],b.[ProdID],a.[chukukucunID],b.[DOEntryID],a.[StorageID],a.[StockQty],b.[Batch],a.[ProduceDate],a.[StockPinZhiID] , [StockTakingid],e.[StoreID]  FROM  [t_DOEntry] b  left join [t_chukukucun] a on a.[DOEntryID] = b.[DOEntryID] ,[t_MOEntry] c,t_DO d ,t_Store e  where   b.[DeliveryNumber] = c.[DONumber]  and    ([StockTakingid] = 0 or [StockTakingid] is null)     and b.[DeliveryNumber] = d.[DeliveryNumber] and e.[StoreName] = d.Stock   ");
            if (where != null && where != "")
            {
                sqlsb.Append(" and " + where);
            }

            dt = App.Query(sqlsb.ToString(), cmd);
            return dt.Tables[0];
        }


        //判断是否能够完成集货（必须所有产品都分配完货位,不发的明细删除）
        public DataTable Getjihuo11(string where, SqlCommand cmd)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append(" SELECT b.[OrderQty],b.[ProdID],a.[chukukucunID],b.[DOEntryID],a.[StorageID],a.[StockQty],b.[Batch],a.[ProduceDate],a.[StockPinZhiID] , [StockTakingid],e.[StoreID]  FROM  [t_DOEntry] b  , [t_chukukucun] a  ,[t_MOEntry] c,t_DO d ,t_Store e  where  a.[DOEntryID] = b.[DOEntryID] and  b.[DeliveryNumber] = c.[DONumber]  and    ([StockTakingid] = 0 or [StockTakingid] is null)     and b.[DeliveryNumber] = d.[DeliveryNumber] and e.[StoreName] = d.Stock   ");
            if (where != null && where != "")
            {
                sqlsb.Append(" and " + where);
            }

            dt = App.Query(sqlsb.ToString(), cmd);
            return dt.Tables[0];
        }


        //获取库存数据。
        public DataTable GetStork(string where,string orderby , SqlCommand cmd)   
        {
            DataSet dt = new DataSet();
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append(" SELECT [StockTakingid],[ProdID],[ProdTypeID],[SupplierID],[StorageID],[Batch],[ProduceDate],[InputDate],[StockPinZhiID],[StockQty],[StockUseableQty],[Remark],[InStockNumber] FROM  [t_StockTaking] ");
            if (where != null && where != "")
            {
                sqlsb.Append(" where " + where);
            }

            if (orderby != null && orderby != "")
            {
                sqlsb.Append("  order by " + orderby);
            }

            dt = App.Query(sqlsb.ToString(), cmd);
            return dt.Tables[0];
        }

        //获取库存数据。
        public DataTable GetStork1(string where  )
        {
            DataSet dt = new DataSet();
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append(" select   b.[StorageName] 库位,c.[ProdCode] 商品编码,c.[ProdName] 商品名称 ,a.[Batch] 批次,CONVERT(VARCHAR(10),a.[ProduceDate],111)  生产日期, a.[StockQty] 数量 ,a.[StockUseableQty] 可用数量 ,d.[StockPinZhiMiaoShu] 品质 ,a.[StockTakingid],a.[ProdID],a.[ProdTypeID],a.[SupplierID],a.[StorageID],a.[StockPinZhiID],a.ProduceDate FROM  [t_StockTaking] a,t_Storage b,[t_Product] c,[t_StockPinZhi] d  where  a.[StorageID]=b.[StorageID] and a.[ProdID] = c.[ProdID] and a.[StockPinZhiID] = d.[StockPinZhiID] ");
            if (where != null && where != "")
            {
                sqlsb.Append(" and " + where); 
            }


            dt = ds.GetRecord(sqlsb.ToString());
            return dt.Tables[0];
        }




        //获取库存数据翻页。
        public DataTable GetStork2(int pageSize, int currentPage, string strWhere, string filedOrder)
        {
         

            DataSet dt = new DataSet();

            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top  " + pageSize + "   b.[StorageName] 库位,c.[ProdCode] 商品编码,c.[ProdName] 商品名称 ,a.[Batch] 批次,CONVERT(VARCHAR(10),a.[ProduceDate],111)  生产日期, a.[StockQty] 数量 ,a.[StockUseableQty] 可用数量 ,d.[StockPinZhiMiaoShu] 品质 ,a.[StockTakingid],a.[ProdID],a.[ProdTypeID],a.[SupplierID],a.[StorageID],a.[StockPinZhiID],a.ProduceDate,a.[SupplierID],e.[SupplierName],a.InputDate FROM  [t_StockTaking] a,t_Storage b,[t_Product] c,[t_StockPinZhi] d ,t_Supplier e  where  a.[StorageID]=b.[StorageID] and a.[ProdID] = c.[ProdID] and a.[StockPinZhiID] = d.[StockPinZhiID] and a.[SupplierID]=e.[SupplierID]  ");


           // strSql.Append(" from  t_Product a ,t_ProductType b ");
            strSql.Append(" and a.StockTakingid not in(select top " + topSize + " a.StockTakingid from  [t_StockTaking] a,[t_Product] c where  a.[ProdID] = c.[ProdID]  ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];

        }

        //获取库存分页信息
        public int GetStork2count(string strWhere)
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*)   from  [t_StockTaking] a,[t_Product] c where  a.[ProdID] = c.[ProdID] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }
      


        //生成合并拣货单明细
        public bool SaveMergeEntry(string sqlcase,string donumber,string mergenumber)
        {
           string sqlstr = " update t_do set statusid='1'" + sqlcase + " where deliverynumber='" + donumber + "'" +
                     " insert into t_moentry (mergenumber,donumber,isdeleted) values ('" + mergenumber + "','" + donumber + "','0')";
           return ds.ExecuteSQL(sqlstr);
        }
        //生成合并拣货单明细
        public int SaveMergeEntry111(string sqlcase, string donumber, string mergenumber, SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = " update t_do set statusid='1'" + sqlcase + " where deliverynumber='" + donumber + "'" +
                      " insert into t_moentry (mergenumber,donumber,isdeleted) values ('" + mergenumber + "','" + donumber + "','0')";
            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }
        //生成合并拣货单头
        public int SaveMergeHead111(string mergenumber, string remark, SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = " insert into t_mo (mergenumber,remark,mergedate) values ('" + mergenumber + "','" + remark + "',GETDATE())";
            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }
        //生成合并拣货单头
        public bool SaveMergeHead(string mergenumber,string remark)
        {
            string sqlstr = " insert into t_mo (mergenumber,remark,mergedate) values ('" + mergenumber + "','"+remark+"',GETDATE())";
            return ds.ExecuteSQL(sqlstr);
        }
        //删除合并拣货单明细
        public void DelMergeEntry(string mergenumber)
        {
            string sqlstr = " delete t_moentry where mergenumber='" + mergenumber + "'";
            ds.ExecuteSQL(sqlstr);
        }
        //删除合并拣货单并更改单据状态
        public bool DeleteMerge(string mergenumber)
        {
            string sqlstr = "update t_moentry set isdeleted='True' where mergenumber='" + mergenumber + "'" +
                           " update t_do set StatusID =0 where DeliveryNumber in (select donumber from t_MOEntry where MergeNumber ='" + mergenumber + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //删除合并拣货单并更改单据状态
        public int DeleteMerge111(string mergenumber, SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = "update t_moentry set isdeleted='True' where mergenumber='" + mergenumber + "'" +
                           " update t_do set StatusID =0 where DeliveryNumber in (select donumber from t_MOEntry where MergeNumber ='" + mergenumber + "')";
            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }

        //合并拣货单完成确认
        public bool UpdateMergeStatus(string mergenumber)
        {
            string sqlstr = " update t_mo set isprodsetted=1 where mergenumber='" + mergenumber + "'" +
                            " update t_DO set StatusID=2 where DeliveryNumber in (select DONumber from t_MOEntry where MergeNumber ='" + mergenumber + "'";

            return ds.ExecuteSQL(sqlstr);
        }

        //合并拣货单完成确认
        public int UpdateMergeStatus111(string mergenumber,string JihuoDate ,SqlConnection conn, SqlCommand cmd)  
        {
            string sqlstr = " update t_mo set isprodsetted=1,JihuoDate ='" + JihuoDate + "' where mergenumber='" + mergenumber + "'" +
                            " update t_DO set StatusID=2,[OutStockTime]='" + JihuoDate + "' where DeliveryNumber in (select DONumber from t_MOEntry where MergeNumber ='" + mergenumber + "')";

            return App.ExecuteSql(sqlstr,null, conn, cmd);
        }

        //修改车号车次
        public int UpdateMergeStatus222(string mergenumber, string VehicleNumber, string VehicleOrder, SqlConnection conn, SqlCommand cmd)
        {
            string sqlstr = " update t_DO set [VehicleNumber]='" + VehicleNumber + "',[VehicleOrder]='" + VehicleOrder + "' where DeliveryNumber in (select DONumber from t_MOEntry where MergeNumber ='" + mergenumber + "')";

            return App.ExecuteSql(sqlstr, null, conn, cmd);
        }
       
        //获取合并拣货单明细数据
        public DataTable GetMergeDO(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = " select d.deliverynumber as 提单号,d.custname as 客户名称,p.prodcode as 产品代码,p.prodname as 产品名称,p.prodtype as 规格,de.orderqty as 数量, p.unit as 单位,d.remark as 备注,p.jianhuoshuxin as 拣货属性 " +
                            " ,b.[StorageName] as 货位,a.[StockQty] as 实发数量,a.[Batch] as 批次,CONVERT(varchar(100),a.[ProduceDate], 111) as 生产日期,c.[StockPinZhiMiaoShu] 品质 " +
                " from t_DO d,t_DOEntry de left join  [t_chukukucun] a on de.[DOEntryID]=a.[DOEntryID] left join [t_Storage] b on a.[StorageID]=b.[StorageID]  left join [t_StockPinZhi] c on a.[StockPinZhiID] =c.[StockPinZhiID] ,t_Product p,t_MOEntry me" +
                            " where d.DeliveryNumber =me.DONumber and d.DeliveryNumber =de.DeliveryNumber and de.ProdID =p.ProdID and me.mergenumber='" + sqlcase + "'";
            dt=ds.GetRecord (sqlstr);
            return dt.Tables[0];
        }


        //获取合并拣货单明细数据(手动指派库存)
        public DataTable GetMergeDOzhipai(string sqlcase)  
        {
            DataSet dt = new DataSet();
            string sqlstr = " select d.deliverynumber as 提单号,d.custname as 客户名称,p.prodcode as 产品代码,p.prodname as 产品名称,de.orderqty as 数量, p.unit as 单位,de.DOEntryID,e.[StoreID],de.ProdID  " +

                "   from t_DO d,t_DOEntry de,t_Product p,t_MOEntry me,t_Store e   " +
                            " where d.DeliveryNumber =me.DONumber and d.DeliveryNumber =de.DeliveryNumber and de.ProdID =p.ProdID and e.[StoreName] = d.Stock   and me.mergenumber='" + sqlcase + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取出库单指派的库存信息 
        public DataTable GetMergeDOkucun(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = " select  b.[StorageName] as 货位,a.[StockQty] as 实发数量,a.[Batch] as 批次,CONVERT(varchar(100),a.[ProduceDate], 111) as 生产日期,c.[StockPinZhiMiaoShu] 品质 ,a.[chukukucunID] 出库库存,a.[StockTakingid] 库存id,a.DOEntryID 出库明细id  " +

                "  from   [t_chukukucun] a  left join [t_Storage] b on a.[StorageID]=b.[StorageID]  left join [t_StockPinZhi] c on a.[StockPinZhiID] =c.[StockPinZhiID]  " +
                            " where  a.DOEntryID='" + sqlcase + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取任务单明细数据
        public DataTable GetTaskDOentry(string taskid)
        {
            DataSet dt = new DataSet();
            string sqlstr = " select d.deliverynumber as 提单号,d.custname as 客户名称,p.prodcode as 产品代码,p.prodname as 产品名称,de.orderqty as 数量,de.weight as 重量,d.remark as 备注,p.jianhuoshuxin as 拣货属性" +
                            " from t_DO d,t_DOEntry de,t_Product p,t_taskentry te" +
                            " where d.DeliveryNumber =de.DeliveryNumber and de.ProdID =p.ProdID and d.DeliveryNumber =te.DeliveryNumber and te.TaskId ='" + taskid + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取合并拣货单打印标志
        public bool GetPrintFlag(string mergenum)
        {
            string sqlstr = "select isprinted from t_mo where mergenumber='" + mergenum + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            bool result =Convert.ToBoolean(dr[0].ToString ());
           
            return result;
        }
        //重置拣货单打印
        public bool ResetMoPrint(string mergenum)
        {
            string sqlstr = "update t_mo set isprinted='False' where mergenumber='" + mergenum + "'";
            return ds.ExecuteSQL(sqlstr);
        }
        //验证合并拣货单号
        public bool CheckMoNumbe(string mergenum)
        {
            bool result;
            string sqlstr = " select * from t_mo where mergenumber='" + mergenum + "'";
                
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result =false ;
            }
            else
            {
                result = true;
            }
            return result;
        }
        //验证提单号
        public bool CheckDoNumbe(string donum)
        {
            bool result;
            string sqlstr = " select * from t_do where DeliveryNumber='" + donum + "'";

            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result = false;
            }
            else
            {
                result = true;
            }
            return result;
        }
        //重置提单打印
        public bool ResetDoPrint(string donum)
        {
            string sqlstr = "update t_do set canbeprinted='True' where DeliveryNumber='" + donum + "'";
            return ds.ExecuteSQL(sqlstr);
        }

        //获取指定合并拣货单已发货的提单数据
        public DataTable GetSentedDO(string mergenum)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.* from t_DO d,t_MOEntry me where d.DeliveryNumber =me.DONumber and d.StatusID =2 and me.mergenumber='" + mergenum + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取拣货单头部数据
        public DataTable GetMergeHead(string mergenum)
        {
            DataSet dt = new DataSet();
            string sqlstr = "SELECT m.MergeNumber, d.VehicleNumber, c.carrierName,COUNT(m.MergeNumber) AS countdo,d.saletype,m.remark" +
                           " FROM  t_Carrier c,t_MOEntry me,t_MO  m,t_DO  d" +
                           " where me.DONumber = d.DeliveryNumber and c.CarrierID = d.CarrierID and me.MergeNumber = m.MergeNumber and m.MergeNumber ='" + mergenum + "'" +
                           " GROUP BY m.MergeNumber, d.VehicleNumber, c.carrierName,d.saletype,m.remark";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取入库单数据
        public DataTable Getinstorkbyid(int instorkidd )
        {
            DataSet dt = new DataSet();
            string sqlstr = " select a.InStockNumber,a.CaigouNumber,a.JieDanDate,a.InStockDate,c.UserName,d.InStockTypemiaoshu, " +
                           "  e.InStockStatusmiaoshu,f.ShortName SupplierName,g.ShortName SupplySupplierName,h.ShortName CarrierName, " +
                           "  i.UserName ShouHuoRen,j.StoreName,a.TuiHuoKeHu,a.TuiHuoRemark,a.Remark,k.ProdCode,k.ProdName,l.ProdTypeName, " +
                           " b.StockQtyYuBao,b.StockQty,k.Unit,k.Weight,k.Volume,n.StockPinZhiMiaoShu,b.ProduceDate,b.Batch,m.StorageName " +
                             " from t_InStock a LEFT join t_User i on a.ShouHuoID = i.UserID  ,t_InStockEntry b left join t_Storage m on b.StorageID = m.StorageID ,t_User c ,t_InStockType d,t_InStockStatus e,t_Supplier f,t_SupplySupplier g, " +
                               " t_Carrier h,t_Store j,t_Product k,t_ProductType l,t_StockPinZhi n " +
                                 " where a.InStockID = b.InStockID and a.UserID = c.UserID  and a.InStockTypeID =d.InStockTypeID " +
                                   " and a.InStockStatusID = e.InStockStatusID and f.SupplierID =a.SupplierID and g.SupplySupplierID = a.SupplySupplierID  " +
                           "  and h.CarrierID = a.CarrierID and a.StoreID = j.StoreID and k.ProdID = b.ProdID " +
                            " and k.ProdTypeID = l.ProdTypeID and  b.StockPinZhiID = n.StockPinZhiID and a.InStockID =   " + instorkidd;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取拣货单明细数据
        public DataTable GetMergeTitle(string mergenum)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select p.supplierid,p.prodid,p.prodcode,p.prodname,p.prodtype,sum(de.OrderQty) as orderqty,sum(de.Weight) as weight ,p.unit,p.qtyonboard,p.prodtypeid,de.batch,p.jianhuoshuxin,p.Volume,p.Weight Weighta ,b.StorageName,sum(a.StockQty) StockQty ,a.Batch Batch1,a.ProduceDate,c.StockPinZhiMiaoShu" +
                            "   from t_Product p,t_MOEntry me,t_DOEntry de,t_chukukucun a left join t_Storage b on a.StorageID = b.StorageID left join t_StockPinZhi c on c.StockPinZhiID =a.StockPinZhiID " +
                            "  where p.ProdID =de.ProdID and me.DONumber =de.DeliveryNumber  and de.DOEntryID=a.DOEntryID     and me.mergenumber='" + mergenum + "'" +
                            " group by p.supplierid,p.prodid,p.prodcode,p.ProdName ,p.ProdType,p.Unit ,p.QtyOnBoard ,de.prodid,p.prodtypeid,de.batch,p.jianhuoshuxin ,b.StorageName,a.Batch,a.ProduceDate,c.StockPinZhiMiaoShu,p.Volume,p.Weight " +
                            "   order by b.StorageName,p.prodid,p.ProdTypeID ,p.ProdType ";  
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取装车清单信息
        public DataTable GetVehilceEntry(string mergenum)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.DeliveryNumber ,d.CustName ,d.DeliveryAddress ,d.VehicleNumber ,d.VehicleOrder ,sum(de.OrderQty ) as Orderqty from t_DO d,t_DOEntry de,t_MOEntry me" +
                          " where d.DeliveryNumber =de.DeliveryNumber and me.DONumber =d.DeliveryNumber and me.MergeNumber ='" + mergenum + "'" +
                          " group by d.DeliveryNumber ,d.CustName ,d.DeliveryAddress ,d.VehicleNumber ,d.VehicleOrder";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];

        }
        //获取入库单
        public DataTable GetInStock(string where) 
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select a.*,b.UserName,c.*,e.*,f.*,g.*,h.*,i.UserName ShouHuoRen from  [t_InStock] a left join [t_User] i on a.[ShouHuoID]=i.[UserID]  , [t_User] b,[t_InStockType] c ,[t_InStockStatus] d,[t_Supplier] e,[t_Carrier] f ,[t_SupplySupplier] g ,[t_Store] h  where a.[UserID] = b.[UserID] and c.[InStockTypeID]= a.[InStockTypeID] and d.[InStockStatusID] = a.[InStockStatusID]  and e.[SupplierID] = a.[SupplierID] and f.[CarrierID] = a.[CarrierID]  and g.[SupplySupplierID] = a.[SupplySupplierID] and h.[StoreID] = a.[StoreID] ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }

        //获取出库单
        public DataTable GetOutStock(string where)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select a.SoldTo,a.AreaID, a.CarrierID,a.SupplierID,b.ShortName SupplierName,a.DeliveryNumber,a.OrderNumber,a.OrderType,a.SaleType,a.OrderDatetime,a.SoldTo,a.CustName,a.DeliveryAddress,c.ShortName CarrierName,a.VehicleNumber,a.Remark,d.AreaName,a.VehicleDate,a.Stock,a.Tihuofangshi,a.Lianxifangshi,a.Lianxidianhua,a.Fapiao,a.Xiadancishu,a.Zhifuxinxi,a.youhui from [t_DO] a ,[t_Supplier] b,[t_Carrier] c,[t_Area] d  where a.SupplierID = b.SupplierID and a.CarrierID = c.CarrierID and a.AreaID = d.AreaID ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }

        //获取入库单明细
        public DataTable GetInStockEntry(string where)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select b.[ProdTypeName] 商品种类,c.[ProdName] 商品名称,c.[ProdCode] 商品编码,a.[StockQtyYuBao] 预报数量,a.[StockQty] 实收数量,c.[Unit] 单位 ,c.[Weight] 吨位,c.[Volume] 立方米,d.[StockPinZhiMiaoShu] 商品状态,a.[ProduceDate] 生产日期,a.[Batch] 批次,a.[ProdID] 商品id,a.[InStockEntryID] 明细id,a.[ProdTypeID] 商品种类id,e.[StorageName] 库位,a.[StorageID] 库位id,a.StockPinZhiID 品质  from [t_InStockEntry] a left join [t_Storage] e on a.[StorageID] = e.[StorageID] ,[t_ProductType] b ,[t_Product] c ,[t_StockPinZhi] d   where a.[ProdTypeID]=b.[ProdTypeID] and c.[ProdID] = a.[ProdID]  and d.[StockPinZhiID] = a.[StockPinZhiID] ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }
        //获取出库单明细
        public DataTable GetOutStockEntry(string where)
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select  c.ProdTypeName 商品种类 ,b.ProdCode 商品编码,b.ProdName 商品名称,a.OrderQty 商品数量,b.Unit 单位,a.Price 商品单价,a.Amount 商品总价 ,b.Weight 重量,b.Volume 体积,a.Batch 批次,a.DeliveryNumber 提单号,b.ProdID 商品id,b.ProdTypeID 商品大类id ,a.DOEntryID 明细id from t_DOEntry a ,t_Product b,t_ProductType c where a.ProdID = b.ProdID and b.ProdTypeID = c.ProdTypeID ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }

        //获取入库单明细
        public DataTable GetInStockEntryqianhe(string where)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select b.[ProdTypeName] 商品种类,c.[ProdName] 商品名称,c.[ProdCode] 商品编码,a.[StockQtyYuBao] 预报数量,a.[StockQty] 实收数量,c.[Unit] 单位 ,c.[Weight] 吨位,c.[Volume] 立方米,d.[StockPinZhiMiaoShu] 商品状态,a.[ProduceDate] 生产日期,a.[Batch] 批次,a.[ProdID] 商品id,a.[InStockEntryID] 明细id,a.[ProdTypeID] 商品种类id,e.[StorageName] 库位,a.[StorageID] 库位id,a.StockPinZhiID 品质id,d.[StockPinZhiMiaoShu] 品质  from [t_InStockEntry] a left join [t_Storage] e on a.[StorageID] = e.[StorageID] ,[t_ProductType] b ,[t_Product] c ,[t_StockPinZhi] d   where a.[ProdTypeID]=b.[ProdTypeID] and c.[ProdID] = a.[ProdID]  and d.[StockPinZhiID] = a.[StockPinZhiID] ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }

        //获取相关提单号
        public string GetCountDo(string mergenum)
        {
            DataSet dt = new DataSet();
            string countdo = "";
            string sqlstr = "select donumber from t_moentry where mergenumber='" + mergenum + "'";
            dt = ds.GetRecord(sqlstr);
            DataTable dtb = dt.Tables[0];
            for (int i = 0; i < dtb.Rows.Count; i++)
            {
                countdo = countdo + dtb.Rows[i][0].ToString() + "/";
            }
           
            return countdo;

        }
        //自动生成合并拣货单号
        public string AutoCreatMergeNumber(string flag)
        {
            
            string dbDateTime = DateTime.Now.ToString("yy");
            string sqlstr = "select max(mergenumber) from t_mo";//获取当前最大的合并拣货单号
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            string maxID =dr[0].ToString ();
            string Result="";
                      
            if (maxID == "")//没有最大编号
            {
                Result = flag + dbDateTime + "000001";
            }
            else
            {
                //截取字符
                string strFirst = maxID.Substring(2, 2);
                string strLast = maxID.Substring(4, 6);
                if (dbDateTime == strFirst)//截取的最大编号的年份是否和数据库服务器系统时间相等
                {
                    string strNewFour = (Convert.ToInt32(strLast) + 1).ToString("000000");//000000+1
                    Result = flag + dbDateTime + strNewFour;
                }
                else
                {
                    Result = flag + dbDateTime + "000001";
                }
            }
           
            return Result;
        }
        //自动生成任务号
        public string AutoCreatTaskID()
        {

            string dbDateTime = DateTime.Now.ToString("yyyyMMdd");
            string sqlstr = "select max(taskid) from t_task";//获取当前最大的任务号
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            string maxID = dr[0].ToString();
            string Result = "";

            if (maxID == "")//没有最大编号
            {
                Result = dbDateTime + "001";
            }
            else
            {
                //截取字符
                string strFirst = maxID.Substring(6, 2);
                string strLast = maxID.Substring(8, 3);
                if (dbDateTime == strFirst)//截取的最大编号的日期是否是当天日期
                {
                    string strNewFour = (Convert.ToInt32(strLast) + 1).ToString("000");//000+1
                    Result = dbDateTime + strNewFour;
                }
                else
                {
                    Result = dbDateTime + "001";
                }
            }

            return Result;
        }
        //根据筛选条件查询提单数据
        public DataTable getDoData(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select Distinct d.deliverynumber as 提单号,d.orderdatetime as 提单日期,d.vehiclenumber as 车号,d.vehicleorder as 车次,c.carriername as 承运商,d.VehicleDate as 排车日期,d.custname as 客户名称,ar.areaname as 区域,d.deliveryaddress as 送货地址,d.remark as 备注" +
                            " from t_do d,t_doentry de,t_product p,t_area ar,t_carrier c" +
                            " where d.deliverynumber=de.deliverynumber and de.prodid=p.prodid and d.carrierid=c.carrierid and d.statusid=0 and d.areaid=ar.areaid " + sqlcase +
                            " order by d.vehiclenumber";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //根据筛选条件查询提单数据(打印)
        public DataTable getDoData11(string sqlcase)     
        {
            DataSet dt = new DataSet();
            string sqlstr = "select Distinct d.deliverynumber as 提单号,d.orderdatetime as 提单日期,d.vehiclenumber as 车号,d.vehicleorder as 车次,c.carriername as 承运商,d.VehicleDate as 排车日期,d.custname as 客户名称,ar.areaname as 区域,d.deliveryaddress as 送货地址,d.remark as 备注" +
                             " from t_do d,t_doentry de,t_product p,t_area ar,t_carrier c" +
                            " where d.deliverynumber=de.deliverynumber and de.prodid=p.prodid and d.carrierid=c.carrierid   and d.areaid=ar.areaid " + sqlcase +
                            " order by d.vehiclenumber";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //根据筛选条件查询出库库单数据
        public DataTable getDoData(int pageSize, int currentPage, string strWhere, string filedOrder)
        {
            DataSet dt = new DataSet();
            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top  " + pageSize + "  d.deliverynumber as 提单号,d.orderdatetime as 提单日期,d.vehiclenumber as 车号,d.vehicleorder as 车次,c.carriername as 承运商,d.VehicleDate as 排车日期,d.custname as 客户名称,ar.areaname as 区域,d.deliveryaddress as 送货地址,d.remark as 备注,e.StatusName as 状态  ");
            strSql.Append("  from t_do d,t_area ar,t_carrier c,t_DOStatus e   ");
            strSql.Append(" where   d.carrierid=c.carrierid  and d.areaid=ar.areaid and d.StatusID = e.StatusID   ");
            strSql.Append(" and d.deliverynumber not in(select top " + topSize + " d.deliverynumber from t_do d  ");
            if (strWhere!=null&&strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }

        //获取商品分页信息
        public int getDoDatacount(string strWhere)
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*)     from t_do d  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("where" + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }

        //根据筛选条件查询入库单数据
        public DataTable getInStorkData(int pageSize, int currentPage, string strWhere, string filedOrder)  
        {
            DataSet dt = new DataSet();
            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top  " + pageSize + "  a.[InStockID] 入库单id  ,a.[InStockNumber] 入库单号,a.[CaigouNumber] 采购单号,a.[JieDanDate] 接单日期,b.[UserName] 制单人 ,c.[InStockTypemiaoshu] 入库类型,e.[SupplierName] 货主,g.[SupplySupplierName] 供应商,f.[CarrierName] 承运商 ,h.[StoreName] 仓库,a.[TuiHuoKeHu] 退货客户,d.[InStockStatusmiaoshu] 入库单状态,a.[InStockStatusID] 状态id ");
            strSql.Append(" from  [t_InStock] a, [t_User] b,[t_InStockType] c ,[t_InStockStatus] d,[t_Supplier] e,[t_Carrier] f ,[t_SupplySupplier] g ,[t_Store] h ");
              strSql.Append(" where a.[UserID] = b.[UserID] and c.[InStockTypeID]= a.[InStockTypeID] and d.[InStockStatusID] = a.[InStockStatusID] and e.[SupplierID] = a.[SupplierID] and f.[CarrierID] = a.[CarrierID]  and g.[SupplySupplierID] = a.[SupplySupplierID] and h.[StoreID] = a.[StoreID]  ");
               strSql.Append(" and a.[InStockID] not in(select top " + topSize + " a.InStockID from [t_InStock] a  " );
               if (strWhere!=null&&strWhere.Trim() != "")
            {
                strSql.Append( " where "+strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append( " and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }


        //根据筛选条件查询转储单数据
        public DataTable getzhuanchuData(int pageSize, int currentPage, string strWhere, string filedOrder)
        {  
            DataSet dt = new DataSet();
            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top  " + pageSize + "  a.shougongNumber 手工单号,a.OrderNumber 系统单号,b.UserName 制单人,Convert(VARCHAR(24), a.qianhedate,111)  单据日期,c.tiaozhengleixingmiaoshu 调整类型 ,a.kucuntiaozhengID ");
            strSql.Append("  from  t_kucuntiaozheng a, [t_User] b,t_tiaozhengleixing c  ");
            strSql.Append("  where a.zhidanren = b.[UserID] and a.tiaozhengleixing= c.tiaozhengleixing   ");
            strSql.Append(" and a.kucuntiaozhengID not in(select top " + topSize + " a.kucuntiaozhengID from t_kucuntiaozheng a  ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }

        //获取商品分页信息
        public int getzhuanchucount(string strWhere)
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*)     from  t_kucuntiaozheng a  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("where" + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }



        
        //获取商品分页信息
        public int getInStorkcount(string strWhere)  
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*)     from [t_InStock] a  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append( "where" +strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }




        //更新打印状态及打印次数
        public void UpdatePrint(string mergenumber)
        {
            
            string sqlstr = "update t_mo set printtimes=printtimes+1,isprinted='True' where mergenumber='" + mergenumber + "'";
            ds.ExecuteSQL(sqlstr);
            
        }

        //获取承运商数据
        public DataTable GetCarrierID()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select carrierid,carriername from t_carrier order by carrierid";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
                //获取提单类型
        public DataTable Gettidanleixin()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select [tidanleixinID],[tidanleixin] from [t_tidanleixin] order by tidanleixinID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取销售类型
        public DataTable Getxiaohouleixin()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select [xiaohouleixinID],[xiaohouleixin] from [t_xiaohouleixin] order by [xiaohouleixinID]";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取支付方式
        public DataTable Getzhifufangshi()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select [zhifufangshiID],[zhifufangshi] from [t_zhifufangshi] order by [zhifufangshiID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取提货方式
        public DataTable Gettihuofangshi()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select [tihuofangshiID],[tihuofangshi] from [t_tihuofangshi] order by [tihuofangshiID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取商品分类数据
        public DataTable GetProductType()  
        {
            DataSet dt = new DataSet();
            string sqlstr = "SELECT [ProdTypeID],[ProdTypeName] FROM [t_ProductType] order by [ProdTypeID]";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取库区
        public DataTable Getcangku(string where)  
        {
            DataSet dt = new DataSet();
            string sqlstr = "SELECT [StoreID] 仓库id ,[StoreName] 仓库名称  FROM [t_Store] ";
            if (where != null && where != "")
            {
                sqlstr += " where " + where;
            }
            sqlstr += "order by [StoreID]";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取库区
        public DataTable Getquyu(string where )
        {
            DataSet dt = new DataSet();
            string sqlstr = "SELECT [KuquID] 库区id ,[KuquName] 库区名称  FROM [t_Kuqu] ";
            if (where != null && where!="")
            {
                sqlstr += " where " + where;
            }
            sqlstr += "order by [KuquID]";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取库位
        public DataTable Getkuwei(string where) 
        {
            DataSet dt = new DataSet();
            string sqlstr = "SELECT [StorageID] 库位id ,[StorageName] 库位名称  FROM  [t_Storage] ";
            if (where != null && where != "")
            {
                sqlstr += " where " + where;
            }
            sqlstr += "order by [StorageID]";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取商品状态
        public DataTable GetStockPinZhi()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [StockPinZhiID],[StockPinZhiMiaoShu],[Remark] FROM [t_StockPinZhi] order by StockPinZhiID";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取入库类型
        public DataTable getcmbsaletype()  
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [InStockTypeID],[InStockTypemiaoshu] FROM [t_InStockType] order by [InStockTypeID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取转储类型
        public DataTable getzhuanchutype()  
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [tiaozhengleixing],[tiaozhengleixingmiaoshu] FROM [t_tiaozhengleixing] order by [tiaozhengleixing] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取销售类型
        public DataTable getxiaoshoutype()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [xiaohouleixinID],[xiaohouleixin] FROM [t_xiaohouleixin]  order by [xiaohouleixinID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取商品分页信息
        public DataTable getproduct(int pageSize, int currentPage, string strWhere, string filedOrder)
        {
            DataSet dt = new DataSet();

            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top  " + pageSize + " a.[ProdID] as 商品id,b.ProdTypeName as 商品类型,a.[ProdCode] as 商品编码,a.[ProdName] as 商品名称,a.[ProdType] as 规格,a.[Unit] as 包装,a.[Volume] as 商品体积,a.[Weight] as 商品重量,a.[UnitQty] as 包装数量,a.[jianhuoshuxin] as 拣货属性,a.[ProdTypeID] as 商品类型id,a.[SupplierID] as 货主id  ");


            strSql.Append(" from  t_Product a ,t_ProductType b ");
            strSql.Append(" where a.ProdTypeID = b.ProdTypeID and ProdID not in(select top " + topSize + " a.ProdID from t_Product a,t_ProductType b where a.ProdTypeID = b.ProdTypeID  ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }
         //获取商品分页信息
        public int getproductcount(string strWhere)
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) carrierid  from t_Product a ,t_ProductType b where a.ProdTypeID = b.ProdTypeID ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }



        //获取仓库
        public DataTable getcangku()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [StoreID],[StoreCode],[StoreName],[StoreAddress],[AreaID],[CarrierID] FROM [t_Store] order by [StoreID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取客户
        public DataTable getkehu()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [kehuID],[M_R_Name],[M_R_M_Name],[M_Phone] FROM [t_kehu] where Userble = 1 order by [kehuID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取客户和地址
        public DataTable getkehu(string  where )
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT a.[kehuID],a.[M_R_Name],a.[M_R_M_Name],a.[M_Phone],b.AreaID,b.AddressName,b.kehudizhiID FROM [t_kehu] a, [t_kehudizhi] b  where a.Userble = 1 and a.kehuID = b.kehuID  and " + where + " order  by [kehuID]  ";
            
            
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }



        //获取货主
        public DataTable gethuozhu() 
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [SupplierID],[SupplierName],[ShortName],[IsNeedPrintDO],[DOReportName] FROM [t_Supplier] order by [SupplierID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //供应商货主
        public DataTable getgongyingshang()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [SupplySupplierID],[SupplySupplierName],[ShortName] FROM [t_SupplySupplier] order by [SupplySupplierID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //入库单状态
        public DataTable getzhuangtai()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [InStockStatusID],[InStockStatusmiaoshu] FROM [t_InStockStatus] order by [InStockStatusID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取提单状态
        public DataTable GetDoStatus()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select statusid,statusname from t_dostatus order by statusid";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取指定合并拣货单汇总信息
        public DataTable GetMergeData(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select m.mergenumber as 拣货单号,d.vehiclenumber as 车号,c.carriername as 承运商,COUNT (m.MergeNumber) as 提单数" +
                          " from t_Carrier c,t_DO d,t_MO m,t_MOEntry me" +
                          " where m.MergeNumber =me.MergeNumber and me.DONumber =d.DeliveryNumber and d.CarrierID =c.CarrierID and me.isdeleted='False'" + sqlcase +
                          " group by m.MergeNumber,d.VehicleNumber ,c.carrierName";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取指定合并拣货单汇总信息
        public DataTable GetMergeData1(string sqlcase)   
        {
            DataSet dt = new DataSet();
            string sqlstr = "select m.mergenumber as 拣货单号,d.vehiclenumber as 车号,c.carriername as 承运商,COUNT (m.MergeNumber) as 提单数 ,'手工分配货位' 手工分配货位 " +
                          " from t_Carrier c,t_DO d,t_MO m,t_MOEntry me" +
                          " where m.MergeNumber =me.MergeNumber and me.DONumber =d.DeliveryNumber and d.CarrierID =c.CarrierID and me.isdeleted='False'" + sqlcase +
                          " group by m.MergeNumber,d.VehicleNumber ,c.carrierName";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //获取区域数据
        public DataTable GetAreaID()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select areaid,areaname from t_area order by areaid";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取需要排车的提单信息
        public DataTable GetVehicleDo(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.DeliveryNumber as 提单号码, d.orderdatetime as 提单日期,d.CustName as 客户名称,d.Deliveryaddress as 送货地址,ar.AreaName as 区域,ca.CarrierName as 承运商,d.Remark as 备注 " +
                          " from t_DO d ,t_Area ar,t_Carrier ca" +
                          " where d.AreaID =ar.AreaID  and d.CarrierID =ca.CarrierID and d.statusid=0 and (d.VehicleNumber is NULL or d.VehicleNumber='') " + sqlcase;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取需要排车的提单信息
        public DataTable GetVehicleDo11111(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.DeliveryNumber as 提单号码, (CAST(DATEPART(mm,d.orderdatetime) AS nvarchar(10))+'/'+CAST(DATEPART(dd,d.orderdatetime) AS nvarchar(10))) as 提单日期,d.CustName as 客户名称,sum(td.OrderQty) as 箱数,sum(td.Weight) as 吨位,d.Deliveryaddress as 送货地址,ar.AreaName as 区域,d.Remark as 备注 " +
                          " from t_DO d ,t_Area ar,t_Carrier ca,t_DOEntry td" +
                          " where d.DeliveryNumber=td.DeliveryNumber and d.AreaID =ar.AreaID  and d.CarrierID =ca.CarrierID and d.statusid=0 and (d.VehicleNumber is NULL or d.VehicleNumber='') " + sqlcase+
                          " group by d.DeliveryNumber,d.orderdatetime,d.CustName,d.Deliveryaddress,ar.AreaName,d.Remark ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取选中箱数和重量
        public DataTable GetQtyWet(string donumber)
        {
            DataSet dt = new DataSet();
            string sqlstr = " select SUM(de.orderqty),SUM (de.weight),SUM(de.OrderQty *pr.Volume) from t_DOEntry de,t_Product pr " +
                          " where de.ProdID =pr.ProdID and de.DeliveryNumber ='" + donumber + "'" +
                          " group by de.deliverynumber";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取时间段数据
        public DataTable GetTimeID()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select timeid,convert(char(15),timefrom)+CONVERT(char(15),timeto) as timename from t_time order by timeid";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //安排车号及预计到达时间
        public void UpdateVehicle(string vehiclenum,string deliverynumber,string vehorder)
        {

            string sqlstr = "update t_do set vehiclenumber='" + vehiclenum + "',vehicleorder='" + vehorder + "',VehicleDate =GETDATE()" + " where deliverynumber='" + deliverynumber + "'";
            ds.ExecuteSQL(sqlstr);

        }
        //获取转储单
        public DataTable kucuntiaozheng(string kucuntiaozhengID)  
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT a.[kucuntiaozhengID],a.[shougongNumber],a.[OrderNumber],a.[zhidanren],a.[qianhedate],a.[remark],a.[tiaozhengleixing],b.[UserName],c.[tiaozhengleixingmiaoshu] " +
                          "  FROM  [t_kucuntiaozheng] a, [t_User] b ,[t_tiaozhengleixing] c  where  a.[zhidanren] = b.[UserID] and a.[tiaozhengleixing] = c.[tiaozhengleixing] " +
                          " and a.kucuntiaozhengID=" + kucuntiaozhengID  ;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取转储单明细
        public DataTable kucuntiaozhengdetail(string kucuntiaozhengID) 
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT b.[SupplierName] 货主,c.[ProdCode] 商品代码,c.[ProdName] 商品名称,d.[StorageName] 原库位,a.[oldProductdate] 原生产日期,a.[oldBatch] 原批次,a.[oldInputDate] 原入库日期,a.[oldStockQty] 原数量,a.[oldUserStockQty] 原可用数量,f.[StockPinZhiMiaoShu] 原品质,e.[StorageName]  现库位,a.[Productdate] 现生产日期,a.[Batch] 现批次,a.[InputDate] 现入库日期,a.[StockQty] 调整数量,g.[StockPinZhiMiaoShu] 现品质 " +
                          "  FROM  [t_kucuntiaozhengdetail] a,[t_Supplier] b ,[t_Product] c ,[t_Storage] d,[t_Storage] e,[t_StockPinZhi] f,[t_StockPinZhi] g " +
                          "  where a.[SupplierID] = b.[SupplierID] and a.[ProdID] = c.[ProdID] and a.[oldStorageID]=d.[StorageID]  and a.[StorageID] = e.[StorageID] and a.[oldStockPinZhiID] = f.[StockPinZhiID] and a.[StockPinZhiID] = g.[StockPinZhiID] and a.kucuntiaozhengID=" + kucuntiaozhengID;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //获取转储单明细( 打印)
        public DataTable kucuntiaozhengdetailprint(string kucuntiaozhengID) 
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT b.[SupplierName] ,c.[ProdCode] ,c.[ProdName] ,d.[StorageName] ,a.[oldProductdate] ,a.[oldBatch] ,a.[oldInputDate] ,a.[oldStockQty] ,a.[oldUserStockQty] ,f.[StockPinZhiMiaoShu],e.[StorageName]  ,a.[Productdate] ,a.[Batch],a.[InputDate] ,a.[StockQty] ,g.[StockPinZhiMiaoShu] ,h.[kucuntiaozhengID],h.[shougongNumber],h.[OrderNumber],h.[zhidanren],h.[qianhedate],h.[remark],h.[tiaozhengleixing],i.[UserName],j.[tiaozhengleixingmiaoshu]  " +
                          "   FROM  [t_kucuntiaozhengdetail] a,[t_Supplier] b ,[t_Product] c ,[t_Storage] d,[t_Storage] e,[t_StockPinZhi] f,[t_StockPinZhi] g   ,[t_kucuntiaozheng] h, [t_User] i ,[t_tiaozhengleixing] j  " +
                          " where a.[SupplierID] = b.[SupplierID] and a.[ProdID] = c.[ProdID] and a.[oldStorageID]=d.[StorageID]  and a.[StorageID] = e.[StorageID] and a.[oldStockPinZhiID] = f.[StockPinZhiID] and a.[StockPinZhiID] = g.[StockPinZhiID] and h.[zhidanren] = i.[UserID] and h.[tiaozhengleixing] = j.[tiaozhengleixing] and a.[kucuntiaozhengID] = h.[kucuntiaozhengID] and a.kucuntiaozhengID=" + kucuntiaozhengID;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取提单明细信息
        public DataTable GetDoEntry(string donumber)
        {
            DataSet dt = new DataSet();
            string sqlstr = " select de.deliverynumber as 提单号,p.prodcode as 产品代码,p.prodname as 产品名称,p.prodtype as 规格,de.orderqty as 数量, de.weight as 重量,p.jianhuoshuxin as 拣货属性" +
                          " from t_DOEntry de,t_Product p" +
                          " where de.ProdID =p.ProdID and de.deliverynumber='" + donumber + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取提单全部信息
        public DataTable GetDo(string donumber)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.DeliveryNumber as 提单号,d.SoldTo as 客户名称,d.OrderDatetime as 提单日期,d.SaleType as 销售类型,d.VehicleNumber as 车号,ds.StatusName as 提单状态" +
                          " from t_DO d,t_DOStatus ds" +
                          " where d.StatusID =ds.StatusID and d.DeliveryNumber ='" + donumber + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取承运商所有时间段的车辆限定数
        public DataTable GetVehicleCnt(string carrierid,string vehdate)
        {
            DataSet dt = new DataSet();
            string sqlstr = " select c.carriername as 承运商,convert(char(15),t.timefrom)+CONVERT(char(15),t.timeto) as 预约时间段,v.vehiclecount as 限定车辆数" +
                          " from t_carrier c,t_time t,t_vehiclecount v"+
                          " where c.carrierid=v.carrierid and v.timeid=t.timeid and v.carrierid='"+carrierid +"' and v.designatedate='"+vehdate +"'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];

        }
        //保存选中时间段和承运商的限定车辆数
        public void SaveVehicleCnt(string carrierid, string timeid, string vehcount,string vehdate)
        {
            
            string sqlstr =" insert t_vehiclecount (carrierid,timeid,vehiclecount,overcount,designatedate) values ('" + carrierid + "','" + timeid + "','" + vehcount + "','"+vehcount +"','"+vehdate +"')";
            ds.ExecuteSQL(sqlstr);
                         
        }
        //获取车辆分配概况信息
        public DataTable GetVehicleNumber(string sqlcmd)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select c.CarrierName,convert(char(15),t.timefrom)+CONVERT(char(15),t.timeto) as timename,v.VehicleCount,v.DesignateDate" +
                          " from t_Carrier c,T_Time t,T_VehicleCount v" +
                          " where c.CarrierID =v.CarrierID and t.TimeID =v.TimeID" + sqlcmd +
                          " order by v.CarrierID ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取车辆分配明细信息
        public DataTable GetVehNumEntry(string sqlcmd)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select c.CarrierName ,vc.VehicleCount ,vc.OverCount ,CONVERT(char(15),t.timefrom)+CONVERT(char(15),t.timeto) as timename,vi.vehiclenumber as VehicleNumber " +
                          " from t_Carrier c,T_VehicleCount vc,T_Time t,t_VehicleNumber vn ,t_vehicleinfo vi" +
                          " where t.TimeID =vn.TimeID and c.CarrierID =vn.CarrierID and vc.CarrierID =vn.CarrierID and vn.vehiclenumber=vi.vehicleid and vc.TimeID =vn.TimeID and vc.DesignateDate =vn.InputDate " + sqlcmd;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取排车提单明细
        public DataTable GetVehDoEntry(string sqlcmd)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.DeliveryNumber ,d.CustName ,d.DeliveryAddress,p.ProdCode,p.ProdName,p.ProdType,p.Unit,ar.AreaName,c.CarrierName ,d.VehicleNumber,d.Remark ,de.OrderQty,de.Weight,p.jianhuoshuxin" +
                         " from t_DO d,t_DOEntry de,t_Area ar,t_Carrier c,t_Product p" +
                         " where d.DeliveryNumber =de.DeliveryNumber and d.AreaID =ar.AreaID and d.CarrierID =c.CarrierID and de.ProdID =p.ProdID " + sqlcmd +
                         " order by d.DeliveryNumber ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取车辆车号
        public DataTable GetVehicleInfo(string carrierid)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select vehicleid,vehiclenumber from t_vehicleinfo where 1=1"+carrierid ;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取某承运商某时间段的车辆数
        public string GetVehCount(string carrierid, string timeid,string vehdate)
        {
            string vehiclecount;
            string sqlstr = "select overcount from t_vehiclecount where carrierid='" + carrierid + "' and timeid='" + timeid + "' and designatedate='"+vehdate +"'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
                {

                    vehiclecount = "0";
                }
                else
                {
                    vehiclecount =dr[0].ToString ();
                }

            
            return vehiclecount;
          }
        //更新承运商车辆控制信息
        public bool UpdateVehCnt(string timeid,string carrierid, string count,string vehdate)
        {
            
            string sqlstr = "update t_vehiclecount set overcount='" + count + "' where timeid='" + timeid + "' and carrierid='" + carrierid + "' and designatedate='" + vehdate + "'";
            return ds.ExecuteSQL(sqlstr);                
           
            
        }
        //保存承运商指派车号信息
        public void SaveVehNumber(string timeid, string carrierid, string vehnumber, string vehdate)
        {
            
            string sqlstr = "insert t_vehiclenumber (carrierid,timeid,inputdate,vehiclenumber) values ('" + carrierid + "','" + timeid + "','" + vehdate + "','" + vehnumber + "')";
            ds.ExecuteSQL(sqlstr);
        }

        //获取回单状态
        public DataTable GetBackid()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select backid,backname from t_backstatus order by backid";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];

        }
        //保存回单信息
        public bool SaveDoBack(string donumber, string backid, string remark)
        {
            
            string sqlstr = "if exists ( select * from t_doback where deliverynumber='" + donumber + "')" +
                          " update t_doback set backid='" + backid + "',remark='" + remark + "' where deliverynumber='" + donumber + "'" +
                          "else" +
                          " insert t_doback (deliverynumber,backid,remark) values ('" + donumber + "','" + backid + "','" + remark + "')";
            return ds.ExecuteSQL(sqlstr);
        
        }
        //获取需要重新排车的提单
        public DataTable GetVehicleEdit(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr=" select d.DeliveryNumber as 提单号码, d.Orderdatetime as 提单日期,d.CustName as 客户名称,d.Deliveryaddress as 送货地址,d.vehiclenumber as 车号,d.vehicleorder as 车次,ar.AreaName as 区域,ca.CarrierName as 承运商,d.Remark as 备注 " +
                          " from t_DO d ,t_Area ar,t_Carrier ca" +
                          " where d.AreaID =ar.AreaID and d.statusid=0 and d.CarrierID =ca.CarrierID and d.VehicleNumber is NOT NULL and d.VehicleNumber<>'' " + sqlcase;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取已冻结提单信息
        public DataTable GetDoLocked()
        {
            DataSet dt = new DataSet();
            string sqlstr = " select deliverynumber as 提单号,custname as 客户名称,deliveryaddress as 送货地址,orderdatetime as 提单日期,remark as 备注" +
                          " from t_do" +
                          " where statusid='4'"+
                          " order by deliverynumber";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取提单状态
        public string GetDoStatus(string donumber)
        {
            string ststus;
            string sqlstr = " select statusid from t_do where deliverynumber='" + donumber + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {

                ststus = "";
            }
            else
            {
                ststus = dr[0].ToString();
            }
            return ststus;
        }
        //更新提单状态
        public bool UpdateDoStaus(string donumber,string statusid)
        {
            string sqlstr = " update t_do set statusid='"+statusid +"' where deliverynumber='" + donumber + "'";
            return ds.ExecuteSQL(sqlstr);
        }
        //获取匹配的承运商编号 
        public int CheckCarrierID(string carriername)
        {
            int carrierid;
            string sqlstr = "select carrierid from t_carrier where carriername='" + carriername + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;
        }
        //获取匹配的产品编号
        public int CheckProdID(string prodcode)
        {
            int prodid;
            string sqlstr = "select prodid from t_product where prodcode='" + prodcode + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                prodid = 0;
            }
            else
            {
                prodid = Int32.Parse(dr[0].ToString());
            }
            return prodid;

         }
        //获取匹配的区域代码
        public int checkAreaid(string areaname)
        {
            int areaid;
            string sqlstr = "select areaid from t_area where areaname='" + areaname + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
               areaid = 0;
            }
            else
            {
                areaid = Int32.Parse(dr[0].ToString());
            }
            return areaid;

        }
        //获取匹配的地址代码
        public int CheckAddressid(string addressname)
        {
            int areaid;
            string sqlstr = "select addressid from t_custaddress where addressname='" + addressname + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                areaid = 0;
            }
            else
            {
                areaid = Int32.Parse(dr[0].ToString());
            }
            return areaid;

        }
        //获取匹配的送货代码
        public string GetCustAddress(string custname)
        {
            string custaddress;
            string sqlstr = "select cuataddress from t_customerqq where custname='" + custname + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                custaddress ="";
            }
            else
            {
                custaddress =dr[0].ToString();
            }
            return custaddress ;

        }
        //检查提单号
        public bool checkDoNumber(string donumber)
        {
            bool result;
            string sqlstr = "select deliverynumber from t_do where deliverynumber ='" + donumber + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result =false ;
            }
            else
            {
                result = true;
            }
            return result;
        }
        //删除提单
        public bool DeleteDo(string donumber)
        {
            string sqlstr = " delete t_do where deliverynumber='" + donumber + "'" +
                          " delete t_doentry where deliverynumber='" + donumber + "'";
            return ds.ExecuteSQL(sqlstr);
        }
        //删除计算运费提单
        public bool DeleteFareDo(string donumber)
        {
            string sqlstr = " delete t_faredo where deliverynumber='" + donumber + "'" +
                          " delete t_faredoentry where deliverynumber='" + donumber + "'";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存标准提单头部信息    
        public bool SaveDoHeard(DeliveryOrder d)
        {
            string sqlstr = "insert into t_do (deliverynumber,ordernumber,ordertype,saletype,ponumber,orderdatetime," +
                    "soldto,deliveryto,custname,deliveryaddress,carrierid,vehiclenumber,remark,areaid,Tihuofangshi,Lianxifangshi,Lianxidianhua,Fapiao,Xiadancishu,Zhifuxinxi,Stock,youhui,BusinessMan) values ('" +
                    d.DeliveryNumber + "','" + d.OrderNumber + "','" + d.OrderType + "','" + d.SaleType + "','" +
                    d.PONumber + "','" + d.OrderDatetime + "','" + d.SoldTo + "','" + d.DeliveryTo + "','" + d.CustName + "','" +
                    d.DeliveryAddress + "','" + d.CarrierID + "','" + d.VehicleNumber + "','" + d.Remark + "','" + d.AreaID + "','" + d.Tihuofangshi + "','" + d.Lianxifangshi + "','" + d.Lianxidianhua + "','" + d.Fapiao + "','" + d.Xiadancishu + "','" + d.Zhifuxinxi + "','" + d.Stock + "','" + d.Youhui + "','" + d.BusinessMan + "' )";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存标准提单明细
        public bool SaveDoEntry(DOEntry de,DeliveryOrder d)
        {
            string sqlstr = "insert into t_doentry (deliverynumber,prodid,orderqty,weight,batch,Price,Amount) values ('" +
                           d.DeliveryNumber + "','" + de.ProdID + "','" + de.OrderQty + "'," + de.Weight + ",'" + de.Batch + "','" + de.Price + "','" + de.Amount + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存斯高迪提单头部信息
        public bool SaveSGDDoHeard(DeliveryOrder d)
        {
            string sqlstr = "insert into t_do (supplierid,deliverynumber,ordernumber,ordertype,saletype,ponumber,orderdatetime," +
                    "soldto,deliveryto,custname,deliveryaddress,carrierid,vehiclenumber,remark,areaid) values ('" +
                    d.SupplierID +"','"+ d.DeliveryNumber + "','" + d.OrderNumber + "','" + d.OrderType + "','" + d.SaleType + "','" +
                    d.PONumber + "','" + d.OrderDatetime + "','" + d.SoldTo + "','" + d.DeliveryTo + "','" + d.CustName + "','" +
                    d.DeliveryAddress + "','" + d.CarrierID + "','" + d.VehicleNumber + "','" + d.Remark + "','" + d.AreaID + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存斯高迪提单明细
        public bool SaveSGDDoEntry(DOEntry de, DeliveryOrder d)
        {
            string sqlstr = "insert into t_doentry (deliverynumber,prodid,orderqty,Price,batch) values ('" +
                           d.DeliveryNumber + "','" + de.ProdID + "','" + de.OrderQty + "','" + de.Price + "','" + de.Batch + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存亲亲提单头部信息
        public bool SaveQQDoHeard(DeliveryOrder d)
        {
            string sqlstr = "insert into t_do (supplierid,deliverynumber,ordernumber,ordertype,saletype,ponumber,orderdatetime," +
                    "soldto,deliveryto,custname,deliveryaddress,carrierid,vehiclenumber,remark,areaid) values ('" +
                    d.SupplierID + "','" + d.DeliveryNumber + "','" + d.OrderNumber + "','" + d.OrderType + "','" + d.SaleType + "','" +
                    d.PONumber + "','" + d.OrderDatetime + "','" + d.SoldTo + "','" + d.DeliveryTo + "','" + d.CustName + "','" +
                    d.DeliveryAddress + "','" + d.CarrierID + "','" + d.VehicleNumber + "','" + d.Remark + "','" + d.AreaID + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存斯高迪提单明细
        public bool SaveQQDoEntry(DOEntry de, DeliveryOrder d)
        {
            string sqlstr = "insert into t_doentry (deliverynumber,prodid,orderqty,batch,price,amount) values ('" +
                           d.DeliveryNumber + "','" + de.ProdID + "','" + de.OrderQty + "','" + de.Batch + "','"+de.Price +"','"+de.Amount +"')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存计算运费提单头部信息
        public bool SaveFareDoHeard(DeliveryOrder d)
        {
            string sqlstr = "insert into t_faredo (deliverynumber,ordernumber,ordertype,saletype,ponumber,orderdatetime," +
                    "soldto,deliveryto,custname,deliveryaddress,carrierid,vehiclenumber,remark,areaid) values ('" +
                    d.DeliveryNumber + "','" + d.OrderNumber + "','" + d.OrderType + "','" + d.SaleType + "','" +
                    d.PONumber + "','" + d.OrderDatetime + "','" + d.SoldTo + "','" + d.DeliveryTo + "','" + d.CustName + "','" +
                    d.DeliveryAddress + "','" + d.CarrierID + "','" + d.VehicleNumber + "','" + d.Remark + "','" + d.AreaID + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存计算运费提单明细
        public bool SaveFareDoEntry(DOEntry de, DeliveryOrder d)
        {
            string sqlstr = "insert into t_faredoentry (deliverynumber,prodid,orderqty,weight,batch) values ('" +
                           d.DeliveryNumber + "','" + de.ProdID + "','" + de.OrderQty + "','" + de.Weight + "','" + de.Batch + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存TPL提单头部信息
        public bool SaveTPLDoHeard(DeliveryOrder d)
        {
            string sqlstr = "insert into t_do (deliverynumber,vehiclenumber,vehicleorder,custname,deliveryaddress," +
                    "vehicledate,arrtimeid,stock,statusid) values ('" +
                    d.DeliveryNumber + "','" + d.VehicleNumber + "','" + d.VehicleOrder + "','" + d.CustName + "','" + d.DeliveryAddress + "','" + d.VehicleDate + "','" + d.ArrTimeID + "','" +d.Stock  +"',5)";
            return ds.ExecuteSQL(sqlstr);
        }
        //保存TPL提单明细
        public bool SaveTPLDoEntry(DOEntry de, DeliveryOrder d)
        {
            string sqlstr = "insert into t_doentry (deliverynumber,prodid,orderqty,weight) values ('" +
                           d.DeliveryNumber + "','" + de.ProdID + "','" + de.OrderQty + "','" + de.Weight + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //检查合并拣货单号
        public bool CheckMergenum(string mergenum)
       {
           bool result;
           string sqlstr = "select mergenumber from t_mo where mergenumber ='" + mergenum + "'";
           DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
           dr.Read();
           if (!dr.HasRows)
           {
               result = false;
           }
           else
           {
               result = true;
           }
           return result;
       }

     //取消排车
        public bool CancelVehnum(string donumber)
        {
            string sqlstr = "update t_do set vehiclenumber='',vehicleorder='' where deliverynumber='" + donumber + "'";
            return ds.ExecuteSQL(sqlstr);
        }

        //获取外部客户提单表头
        public DataTable GetCustDoHead(string donumber)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.DeliveryNumber ,d.CustName ,d.OrderNumber ,d.CustCode ,d.BusinessMan ,d.DeliveryAddress ,ca.CarrierName " +
                          " from t_DO d,t_Carrier ca " +
                          " where d.CarrierID =ca.CarrierID and d.DeliveryNumber='" + donumber + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取外部客户提单明细
        public DataTable GetCustDoEntry(string donumber)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.PrintTimes,d.OrderDatetime,s.SupplierName,d.DeliveryNumber,d.OrderNumber,d.BusinessMan,d.CustCode,d.CustName,d.DeliveryAddress,d.PONumber,d.VehicleNumber,c .CarrierName," +
                "p.ProdCode ,p.ProdName ,p.ProdType ,case when p.IsDummy=1 then 0 else de.OrderQty end as OrderQty ,(de.OrderQty * p.Weight) as orderweight,de.Price ,de.NoTaxAmt,de.Tax,de.Amount, " +
                " p.jianhuoshuxin,d.zhifuxinxi,d.xiadancishu,d.fapiao,d.lianxidianhua,d.lianxifangshi,d.tihuofangshi,d.Remark,d.youhui  " +
                "from t_do d,t_DOEntry de,t_Product p,t_Carrier c,t_Supplier s "+
                "where d.DeliveryNumber=de.DeliveryNumber "+
                "and p.ProdID =de.ProdID "+
                "and d.CarrierID=c.CarrierID "+
                "and d.SupplierID=s.SupplierID "+
                "and de.deliverynumber='"+donumber+"'";
            dt=ds.GetRecord (sqlstr );
            return dt.Tables [0];
        }
        //获取外部客户斯高迪提单明细
        public DataTable GetSGDDoEntry(string donumber)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select s.SupplierName,d.DeliveryNumber,d.OrderNumber,d.BusinessMan,d.CustCode,d.CustName,d.DeliveryAddress,d.PONumber,d.VehicleNumber,c .CarrierName," +
                "p.ProdCode ,p.ProdName ,p.ProdType ,(de.OrderQty / p.UnitQty ) as OrderQty ,(de.OrderQty * p.Weight /1000) as orderweight,de.Price ,case when de.NoTaxAmt is null then 0.0001 end as NoTaxAmt,case when de.Tax IS null then 0 end Tax,de.Amount " +
                "from t_do d,t_DOEntry de,t_Product p,t_Carrier c,t_Supplier s " +
                "where d.DeliveryNumber=de.DeliveryNumber " +
                "and p.ProdID =de.ProdID " +
                "and d.CarrierID=c.CarrierID " +
                "and d.SupplierID=s.SupplierID " +
                "and de.deliverynumber='" + donumber + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取提单排车信息报表明细
        public DataTable GetDoEntryVeh(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "   select distinct d.*,c.CarrierName ,p.ProdCode ,p.ProdName ,p.ProdType ,p.Unit ,de.OrderQty ,de.Weight ,de.Batch ,ar.AreaName,m.MergeDate,p.jianhuoshuxin,di.DriverName,di.DriverMobile,de.[Price],de.[OrderQty]*de.[Price] jine  " +
                          "  from t_DO d left join  t_MOEntry me on d.DeliveryNumber =me.DONumber left join  t_MO m on me.MergeNumber =m.MergeNumber ,t_DOEntry de,t_Carrier c,t_Product p,t_Area ar,t_VehicleInfo vi left join t_VehicleDriver vd  on vi.VehicleID =vd.VehicleID  left join t_DriverInfo di on di.DriverID =vd.DriverID   " +
                          " where d.DeliveryNumber =de.DeliveryNumber" +
                          " and d.CarrierID =c.CarrierID" +
                          " and d.AreaID =ar.AreaID" +
                          " and de.ProdID =p.ProdID" +
                          " and d.VehicleNumber =vi.VehicleNumber" +

                           sqlcase;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取提单排车信息报表汇总
        public DataTable GetDoCount(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select d.VehicleDate, c.CarrierName ,d.VehicleNumber ,d.VehicleOrder,ds.StatusName,count(d.DeliveryNumber ) as docount" +
                         " from t_DO d,t_Carrier c,t_DOStatus ds" +
                         " where d.CarrierID =c.CarrierID"+
                         " and d.StatusID =ds.StatusID"+
                         " and d.VehicleNumber !='' "+ sqlcase +
                         " group by d.VehicleDate, c.CarrierName ,d.VehicleNumber ,d.VehicleOrder,ds.StatusName" +
                         " order by d.VehicleNumber ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取捡货单汇总信息
        public DataTable GetMOCount(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr1 = "select m.MergeNumber ,m.MergeDate ,me.DONumber,'IsProdSetted'=" +
                          " case" +
                          " when m.IsProdSetted =1 then '已集货'" +
                          " when m.IsProdSetted =0 then '未集货'" +
                          " end" +
                          " into temp" +
                          " from t_MO m,t_MOEntry me" +
                          " where m.MergeNumber =me.MergeNumber " + sqlcase;
            ds.ExecuteSQL(sqlstr1);
            string sqlstr2 = "select temp.MergeNumber,temp.mergedate,c.CarrierName ,d.VehicleNumber ,d.VehicleOrder ,a.docount ,temp.isprodsetted,sum(de.OrderQty) as orderqty,sum(de.Weight) as weight" +
                           " from t_DO d, t_DOEntry de,t_Carrier c,temp,(select MergeNumber ,count (DONumber) as docount from t_MOEntry" +
                           " where MergeNumber in (select MergeNumber from temp) group by mergenumber) a" +
                           " where temp.donumber=d.DeliveryNumber and d.DeliveryNumber =de.DeliveryNumber and d.CarrierID =c.CarrierID and temp.MergeNumber=a.MergeNumber" +
                           " group by temp.MergeNumber,temp.mergedate,c.CarrierName ,d.VehicleNumber ,d.VehicleOrder ,temp.isprodsetted,a.docount";
            dt = ds.GetRecord(sqlstr2);
            string sqlstr3 = "drop table temp";
            ds.ExecuteSQL(sqlstr3);
            return dt.Tables[0];
        }

        //获取TPL导入提单信息
        public DataTable GetTPLDoInfo()
        {
            string sqlstr = "select DeliveryNumber,CustName ,DeliveryAddress ,VehicleNumber,VehicleOrder ,VehicleDate ,ArrTimeID ,Stock" +
                            " from t_DO" +
                            " where statusid=5" +
                            " order by custname,vehiclenumber,vehicleorder,vehicledate";
            DataSet dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];

        }
        //获取未发布任务汇总信息
        public DataTable GetUnPushedTask()
        {
            string sqlstr = "select distinct t.TaskID '任务号' ,d.VehicleNumber '车号' ,d.VehicleDate '预约日期' ,d.ArrTimeID '时间段',d.Stock '提货仓库',d.CustName '客户名称' ,d.DeliveryAddress '送货地址' ,b.countdo '提单数' ,a.OrderQty '数量',a.Weight '重量',t.Remark '备注'" +
                           " from t_Task t,t_TaskEntry te,t_DO d,t_DOEntry de," +
                           " (select te.TaskId ,sum(de.OrderQty)as orderqty ,sum(de.Weight) as weight" +
                           " from t_TaskEntry te ,t_DOEntry de" +
                           " where te.DeliveryNumber =de.DeliveryNumber" +
                           " group by te.TaskId ) a," +
                           " (select TaskId ,COUNT(DeliveryNumber ) as countdo" +
                           " from t_TaskEntry" +
                           " group by TaskId ) b" +
                           " where t.TaskID =te.TaskId and d.DeliveryNumber =de.DeliveryNumber and d.DeliveryNumber=te.DeliveryNumber and a.TaskId =t.TaskID and t.TaskID =b.TaskId and t.IsPushed =0";
            DataSet dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }

        //保存车辆任务头记录
        public bool SaveTaskDead(string taskid)
        {
            string sqlstr="insert t_task (taskid) values ('"+taskid +"')";
            return ds.ExecuteSQL(sqlstr);

        }
        //保存车辆任务明细记录
        public bool SaveTaskEntry(string taskid,string deliverynumber)
        {
            string sqlstr = "insert t_taskentry (taskid,DeliveryNumber) values ('" + taskid + "','"+deliverynumber +"')";
            return ds.ExecuteSQL(sqlstr);

        }
        //删除车辆任务记录
        public bool DelTask(string taskid)
        {
            string sqlstr = "delete t_task where taskid='" + taskid + "'" +
                          " delete t_taskentry where taskid='" + taskid + "'";
            return ds.ExecuteSQL(sqlstr);
        }
        //根据合并捡货单修改提单车号
        public bool UpdateVehnumberByMonumber(string monumber,string vehnumber)
        {
            string sqlstr = "update t_do set vehiclenumber='" + vehnumber + "' where deliverynumber in (select donumber from t_moentry where mergenumber='" + monumber + "')";
            return ds.ExecuteSQL(sqlstr);
        }
        //发布任务
        public bool TaskPushed( string taskid,string remark)
        {
            string sqlstr = "update t_task set ispushed=1,remark='" + remark + "' where taskid='" + taskid + "'";
            return ds.ExecuteSQL(sqlstr);
        }
    }

 }

    



